﻿using System.Data;
using System.Windows.Forms;
using Common;
using StedySoft.SenseSDK;
using StedySoft.SenseSDK.DrawingCE;
using StedySoft.SenseSDK.Localization;
namespace MLogix
{

    internal sealed class DBOPS
    {
        
        private static DB filterDB = new DB(Win32.location);
        private static DataTable dummy;
        

        internal sealed class SenseMessagesDB
        {
            //public delegate void D(bool a);
            //public static event D handlechange;
            #region VARS
            private static DataTable allMessages;
            private static DataTable allSentMessages;
            static SensePanelMoreItem messageListCopy;
            static SensePanelMoreItem messageListCopy2;
            static string contactCopy = string.Empty;
            static SenseListControl mainListCopy;
            static SensePanelMoreItem mainListCopy2;
            static bool readmode = false;
            static SenseListControl main2;
            #endregion VARS

            #region DBCALLS
            /// <summary>
            /// Gets all messages stored in DB
            /// </summary>
            internal static void GetAllMessages()
            {
                filterDB.SetConnection("SELECT Sent, Body,Name,Number FROM Messages", DB.sqlAction.Select, out allMessages);
            }
            /// <summary>
            /// Deletes selected messages from the DB
            /// </summary>
            /// <param name="itemRow"></param>
            internal static void DeleteSelectedMessage(SensePanelItem itemRow)
            {
                filterDB.SetConnection("DELETE FROM Messages WHERE Sent = '" + itemRow.SecondaryText.Trim() + "' AND Body = '" + CryptLib.SimpleConfuscator(itemRow.PrimaryText.Trim()) + "'", DB.sqlAction.Delete, out dummy);
                GetAllMessages();

            }
            /// <summary>
            /// Deletes selected new message from DB
            /// </summary>
            /// <param name="itemRow"></param>
            internal static void DeleteFromNewMessages(SensePanelItem itemRow)
            {
                filterDB.SetConnection("DELETE FROM NewMessages WHERE Name = '" + CryptLib.SimpleConfuscator(itemRow.PrimaryText.Trim()) + "' AND Body = '" + CryptLib.SimpleConfuscator(itemRow.SecondaryText.Trim()) + "'", DB.sqlAction.Delete, out dummy);
            }
            /// <summary>
            /// Clears all the New Messages from DB
            /// </summary>
            internal static void ClearNewMessages()
            {
                filterDB.SetConnection("DELETE FROM NewMessages", DB.sqlAction.Delete, out dummy);
            }
            /// <summary>
            /// Saves the Sent messages to DB
            /// </summary>
            /// <param name="name"></param>
            /// <param name="address"></param>
            /// <param name="body"></param>
            /// <param name="time"></param>
            internal static void SaveSentSMS(string name, string address, string body, string time)
            {
                filterDB.SetConnection("INSERT INTO SentMessages(Name,Number,Body,Sent) VALUES('" + name + "','" + address + "','" + body + "','" + time + "')", DB.sqlAction.Insert, out dummy);
            }
            /// <summary>
            /// Select all the sent messages from DB
            /// </summary>
            internal static void GetAllSentSMS()
            {
                filterDB.SetConnection("SELECT Sent, Body,Name,Number FROM SentMessages", DB.sqlAction.Select, out allSentMessages);
            }
            /// <summary>
            /// Delete selected sent message from DB
            /// </summary>
            /// <param name="itemRow"></param>
            internal static void DeleteSentSMS(SensePanelItem itemRow)
            {
                filterDB.SetConnection("DELETE FROM SentMessages WHERE Body = '" + CryptLib.SimpleConfuscator(itemRow.PrimaryText.Trim()) + "' AND Sent = '" + itemRow.SecondaryText.Trim() + "'", DB.sqlAction.Delete, out dummy);
                GetAllSentSMS();
            }
            /// <summary>
            /// Delete all received smses from the contact
            /// </summary>
            /// <param name="contactName">Name of the contact</param>
            internal static void DeleteAllReceivedContactSMS(string contactName)
            {
                filterDB.SetConnection("DELETE FROM Messages WHERE Name = '" + CryptLib.SimpleConfuscator(contactName.Trim()) + "'", DB.sqlAction.Delete, out dummy);
                GetAllMessages();
            }
            /// <summary>
            /// Delete all smses sent to contact
            /// </summary>
            /// <param name="contactName">Name of the contact</param>
            internal static void DeleteAllSentContactSMS(string contactName)
            {
                filterDB.SetConnection("DELETE FROM SentMessages WHERE Name = '" + CryptLib.SimpleConfuscator(contactName.Trim()) + "'", DB.sqlAction.Delete, out dummy);
                GetAllSentSMS();
            }
            #endregion DBCALLS

            #region METHODS
            //Fills the dropdownlist
            internal static void FillContactList(ref SensePanelComboItem ddlContacts)
            {
                DataTable contacts;
                ddlContacts.Items.Clear();
                //filterDB.SetConnection("SELECT DISTINCT(Name) FROM Messages", DB.sqlAction.Select, out contacts);
                filterDB.SetConnection("SELECT Name FROM Messages UNION SELECT Name FROM SentMessages", DB.sqlAction.Select, out contacts);
                string name = string.Empty;
                if (contacts.Rows.Count > 0)
                {
                    ddlContacts.Items.Add(new SensePanelComboItem.Item("(Choose Contact)", "(Choose Contact)"));
                    foreach (DataRow row in contacts.Rows)
                    {
                        name = CryptLib.SimpleDeConfuscator(row[0].ToString().Trim()).Trim();
                        ddlContacts.Items.Add(new SensePanelComboItem.Item(name, name));
                    }
                }
                contacts.Dispose();
                GetAllMessages();
                GetAllSentSMS();
            }


            internal static void GetAllMessagesFromContact(string contact, ref SensePanelMoreItem messageList, ref SenseListControl mainList)
            {
                messageList.ProcessEntireButton = true;
                contactCopy = contact;
                messageListCopy = messageList;
                mainListCopy = mainList;
                DataRow[] messages = allMessages.Select("Name = '" + CryptLib.SimpleConfuscator(contact.Trim()).Trim() + "'");
                if (messages.Length > 0)
                {
                    int countMessage = 0;
                    SensePanelItem item;
                    foreach (DataRow row in messages)
                    {
                        item = new SensePanelItem("SMSCount" + countMessage.ToString());
                        countMessage++;
                        item.PrimaryText = CryptLib.SimpleDeConfuscator(row[1].ToString());
                        item.SecondaryText = row[0].ToString();
                        item.Tag = contact;
                        
                        item.OnClick += new SensePanelItem.ClickEventHandler(itm_OnClick);
                        messageList.Children.Add(item);

                    }
                    messageList.Children.Reverse();
                }

            }

            internal static void GetAllMessagesFromContact(string contact, ref System.Collections.Generic.List<string> CSVList)
            {
                contactCopy = contact;
                DataRow[] messages = allMessages.Select("Name = '" + CryptLib.SimpleConfuscator(contact.Trim()).Trim() + "'");
                if (messages.Length > 0)
                {
                    //Sent, Body,Name,Number
                    string time = string.Empty;
                    string message = string.Empty;
                    string name = string.Empty;
                    string number = string.Empty;
                    foreach (DataRow row in messages)
                    {
                        message = CryptLib.SimpleDeConfuscator(row[1].ToString()).Replace("\r", "").Replace("\n", "");
                        time  = row[0].ToString();
                        name = contact;
                        number = CryptLib.SimpleDeConfuscator(row[3].ToString());
                        CSVList.Add(name + "~" + message + "~" + time + "~" + number);
                    }
                }

            }


            internal static void GetSelectedMessage(ListViewItem itemRow, ref TextBox txtMessage)
            {
                if (itemRow != null)
                {
                    txtMessage.Text = "";
                    txtMessage.Text = itemRow.SubItems[1].Text;
                }
            }



            internal static string GetContactsNumber(string contactName)
            {
                DataRow[] messages = allMessages.Select("Name = '" + CryptLib.SimpleConfuscator(contactName.Trim()).Trim() + "'");
                if (messages.Length > 0)
                {
                    return CryptLib.SimpleDeConfuscator(messages[0][3].ToString().Trim());
                }
                return string.Empty;

            }
 
            internal static void GetNewMessages(ref SensePanelMoreItem mainList)
            {
                mainListCopy2 = mainList;
                DataTable newMessages;
                filterDB.SetConnection("SELECT Sent, Body,Name,Number FROM NewMessages", DB.sqlAction.Select, out newMessages);
                readmode = false;
                if (newMessages.Rows.Count > 0)
                {
                    int countMessages = 0;
                    SensePanelItem newSMSItem;
                    foreach (DataRow row in newMessages.Rows)
                    {
                        newSMSItem = new SensePanelItem("NewSMS" + countMessages.ToString());
                        newSMSItem.PrimaryText = CryptLib.SimpleDeConfuscator(row["Name"].ToString());
                        newSMSItem.Tag = CryptLib.SimpleDeConfuscator(row["Number"].ToString());
                        newSMSItem.SecondaryText = CryptLib.SimpleDeConfuscator(row["Body"].ToString());
                        newSMSItem.OnClick += new SensePanelItem.ClickEventHandler(newSMSItem_OnClick);
                        mainList.Children.Add(newSMSItem);

                        countMessages++;

                    }
                }
                newMessages.Dispose();
            }



            internal static void GetReadModeNewMessages(ref SenseListControl mainControl)
            {
                main2 = mainControl;
                DataTable newMessages;
                filterDB.SetConnection("SELECT Sent, Body,Name,Number FROM NewMessages", DB.sqlAction.Select, out newMessages);
                readmode = true;
                if (newMessages.Rows.Count > 0)
                {
                    int countMessages = 0;
                    SensePanelItem newSMSItem;
                    foreach (DataRow row in newMessages.Rows)
                    {
                        newSMSItem = new SensePanelItem("NewSMS" + countMessages.ToString());
                        newSMSItem.PrimaryText = CryptLib.SimpleDeConfuscator(row["Name"].ToString());
                        newSMSItem.SecondaryText = CryptLib.SimpleDeConfuscator(row["Body"].ToString());
                        newSMSItem.OnClick += new SensePanelItem.ClickEventHandler(newSMSItem_OnClick);
                        mainControl.AddItem(newSMSItem);
                        countMessages++;

                    }
                }
                newMessages.Dispose();
            }

            internal static void GetAllSentMessagesToContact(string contact, ref SensePanelMoreItem messageList, ref SenseListControl mainList)
            {
                messageList.ProcessEntireButton = true;
                contactCopy = contact;
                messageListCopy2 = messageList;
                mainListCopy = mainList;
                DataRow[] messages = allSentMessages.Select("Name = '" + CryptLib.SimpleConfuscator(contact.Trim()).Trim() + "'");
                if (messages.Length > 0)
                {
                    int countMessage = 0;
                    SensePanelItem sentItem;
                    foreach (DataRow row in messages)
                    {
                        sentItem = new SensePanelItem("SentSMSCount" + countMessage.ToString());
                        countMessage++;
                        sentItem.PrimaryText = CryptLib.SimpleDeConfuscator(row[1].ToString());
                        sentItem.SecondaryText = row[0].ToString();
                        sentItem.Tag = contact;
                        sentItem.OnClick += new SensePanelItem.ClickEventHandler(sntItm_OnClick);
                        messageList.Children.Add(sentItem);

                    }
                    messageList.Children.Reverse();
                }
            }
            internal static void GetAllSentMessagesToContact(string contact, ref System.Collections.Generic.List<string> CSVList)
            {
                DataRow[] messages = allSentMessages.Select("Name = '" + CryptLib.SimpleConfuscator(contact.Trim()).Trim() + "'");
                if (messages.Length > 0)
                {
                    //Sent, Body,Name,Number
                    string time = string.Empty;
                    string message = string.Empty;
                    string name = string.Empty;
                    string number = string.Empty;
                    foreach (DataRow row in messages)
                    {
                        message = CryptLib.SimpleDeConfuscator(row[1].ToString()).Replace("\r", "").Replace("\n", "");
                        time =  row[0].ToString();
                        name = contact;
                        number = CryptLib.SimpleDeConfuscator(row[3].ToString());
                        CSVList.Add(name + "~" + message + "~" + time + "~" + number);
                    }
                }
            }

            #endregion METHODS

            #region EVENTS
            internal static void itm_OnClick(object Sender)
            {
                string action = "NA";
                new MessageEditPanel(Sender, out action);
                if (action.Equals("DEL"))
                    ThreadDelMessage();
            }


            internal static void ThreadDelMessage()
            {
                mainListCopy.BeginUpdate();
                messageListCopy.Children.Clear();
                GetAllMessagesFromContact(contactCopy, ref messageListCopy, ref mainListCopy);

                mainListCopy.ShowParent();
                mainListCopy.ShowChildren();
                messageListCopy.SecondaryText = contactCopy + " (" + messageListCopy.Children.Count.ToString() + ")";
                mainListCopy.EndUpdate();
            }

            static private volatile bool messageSent;
            //internal static void SetFormVariable(ref bool sent)
            //{
            //    messageSent = sent;
            //}

            internal static void sntItm_OnClick(object Sender)
            {
                SenseStrings.Cancel = ReadCustomText.GetString("T_DELETE"); 
                SenseStrings.Cancel.CompareTo(ReadCustomText.GetString("T_DELETE"));
                //TODO:
                if (SenseAPIs.SenseMessageBox.Show((Sender as SensePanelItem).PrimaryText, ReadCustomText.GetString("T_SENTAT") + " " + (Sender as SensePanelItem).SecondaryText, SenseMessageBoxButtons.OKCancel) == DialogResult.Cancel)
                {
                    if (SenseAPIs.SenseMessageBox.Show(ReadCustomText.GetString("T_DELTHISSMS"), "SMS FIlter", SenseMessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        mainListCopy.BeginUpdate();
                        DeleteSentSMS((Sender as SensePanelItem));
                        messageListCopy2.Children.Clear();
                        GetAllSentMessagesToContact(contactCopy, ref messageListCopy2, ref mainListCopy);
                        mainListCopy.ShowParent();
                        mainListCopy.ShowChildren();
                        messageListCopy2.SecondaryText = contactCopy + " (" + messageListCopy2.Children.Count.ToString() + ")";
                        mainListCopy.EndUpdate();
                    }
                }
            }

            internal static void newSMSItem_OnClick(object Sender)
            {
                SenseStrings.Cancel = ReadCustomText.GetString("T_REPLY");
                SenseStrings.Cancel.CompareTo(ReadCustomText.GetString("T_REPLY"));
                SensePanelItem itm = Sender as SensePanelItem;
                if (StedySoft.SenseSDK.DrawingCE.SenseAPIs.SenseMessageBox.Show(itm.SecondaryText.ToString(), itm.PrimaryText.ToString().Trim() + " " + ReadCustomText.GetString("T_SAYS"), SenseMessageBoxButtons.OKCancel) == DialogResult.OK)
                {
                    DeleteFromNewMessages(itm);
                    if (!readmode) { mainListCopy2.Children.Remove(itm); } else { main2.RemoveItem(itm); }
                }
                else
                {
                    string action = "NA";
                    new MessageEditPanel(itm.Tag + "|" + itm.PrimaryText, out action);
                }
                SenseStrings.Cancel = ReadCustomText.GetString("T_DELETE");
            }

            #endregion EVENTS

            #region PROPERTIES
                       
            public static bool Messagesent
            {
                get
                {
                    return messageSent;
                }
                set
                {
                    messageSent = value;
                }
            }
            
            #endregion PROPERTIES

            static SenseMessagesDB()
            {
                if (messageSent)
                    Messagesent = messageSent;
            }
        }

        internal sealed class SenseFilterDB
        {

            internal static void FillFilters(ref SensePanelMoreItem messageFilterPanel, ref SensePanelMoreItem contactFilterPanel)
            {
                filterDB.SetConnection("SELECT * FROM Filter", DB.sqlAction.Select, out dummy);
                if (dummy.Rows.Count > 0)
                {
                    SensePanelItem pi;
                    string filterC = string.Empty;
                    string filterM = string.Empty;
                    foreach (DataRow row in dummy.Rows)
                    {
                        filterC = row[0].ToString().Trim();
                        filterM = row[1].ToString().Trim();
                        if (!string.IsNullOrEmpty(filterC))
                        {
                            pi = new SensePanelItem(filterC);
                            pi.PrimaryText = filterC;
                            pi.SecondaryText = ReadCustomText.GetString("T_CLICKTODELETE");// "Click to delete";
                            pi.Tag = "USER";
                            contactFilterPanel.Children.Add(pi);
                        }
                        if (!string.IsNullOrEmpty(filterM))
                        {
                            pi = new SensePanelItem(filterM);
                            pi.PrimaryText = filterM;
                            pi.SecondaryText = ReadCustomText.GetString("T_CLICKTODELETE");
                            pi.Tag = "MESSAGE";
                            messageFilterPanel.Children.Add(pi);
                        }
                    }
                }
            }

            internal static void AddMessageFilter(string filter)
            {
                filterDB.SetConnection("INSERT INTO Filter(Message) VALUES('" + filter.Trim() + "')", DB.sqlAction.Insert, out dummy);
            }

            internal static void AddContactFilter(string filter)
            {
                filterDB.SetConnection("INSERT INTO Filter(Name) VALUES('" + filter.Trim() + "')", DB.sqlAction.Insert, out dummy);
            }

            internal static void DeleteMessageFilter(string filter)
            {
                filterDB.SetConnection("DELETE FROM Filter WHERE Message= '" + filter.Trim() + "'", DB.sqlAction.Delete, out dummy);
            }

            internal static void DeleteContactFilter(string filter)
            {
                filterDB.SetConnection("DELETE FROM Filter WHERE Name= '" + filter.Trim() + "'", DB.sqlAction.Delete, out dummy);
            }
        }

        internal sealed class SensePasswordDB
        {
            internal static void InsertPassword()
            {
                filterDB.SetConnection("INSERT INTO Pass(Password) VALUES('" + CryptLib.EncryptMessage.Trim() + "')", DB.sqlAction.Insert, out dummy);
            }
            internal static void UpdatePassword(string oldPass)
            {
                filterDB.SetConnection("UPDATE Pass SET Password = '" + CryptLib.EncryptMessage.Trim() + "' WHERE Password = '" + oldPass.Trim() + "'", DB.sqlAction.Update, out dummy);
            }
            internal static bool SelectPassword(out string password)
            {
                password = string.Empty;
                filterDB.SetConnection("SELECT * FROM Pass", DB.sqlAction.Select, out dummy);
                if (dummy.Rows.Count > 0)
                {
                    password = dummy.Rows[0][0].ToString().Trim();
                    return true;
                }
                else
                {
                    //StedySoft.SenseSDK.DrawingCE.SenseAPIs.SenseMessageBox.Show("Please Create a password.", "SMS Filter", SenseMessageBoxButtons.OK);
                    return false;
                }
            }
        }
    }
}
